﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using Database.Classes;
using System.Collections.Generic;
using Database;

public partial class UserDefinedFunctions
{
    public static List<int> GetPlayerIDs()
    {
        List<int> tmp = new List<int>();
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Select PlayerID From Players Where PlayerId > 1", conn))
            {

                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    tmp.Add(rdr.GetInt32(0));
                }
            }
        }
        return tmp;
    }
    public static double GetBonusForProduction(int pId)
    {
        double bonus = 0;
        //get bonus from race
        double prodBonus = double.Parse(StoredProcedures.GetConfigurationForOption("ProductionBonus"));
        bonus += prodBonus * GetPlayerInt(pId, "Production").Value;
        string artifact = UsedArtifact(pId);
        switch (artifact)
        {
            case "CS": bonus += 0.1; break;
            case "RF": bonus += 0.2; break;
            case "NF": bonus += 0.3; break;
            case "STr": bonus -= 0.15; break;
        }
        return bonus;
    }
    public static double GetBonusForCulture(int pId)
    {
        double bonus = 0;
        //get bonus from race
        double prodBonus = double.Parse(StoredProcedures.GetConfigurationForOption("CultureBonus"));
        bonus += prodBonus * GetPlayerInt(pId, "Culture").Value;
        string artifact = UsedArtifact(pId);
        switch (artifact)
        {
            case "GT": bonus += 0.1; break;
            case "ITV": bonus += 0.2; break;
            case "STr": bonus += 0.1; break;
            case "BHS": bonus -= 0.1; break;
            case "AF": bonus -= 0.3; break;
            case "MOT": bonus += 0.2; break;
        }
        return bonus;
    }
    public static double GetBonusForScience(int pId)
    {
        double bonus = 0;
        //get bonus from race
        double prodBonus = double.Parse(StoredProcedures.GetConfigurationForOption("ScienceBonus"));
        bonus += prodBonus * GetPlayerInt(pId, "Science").Value;
        string artifact = UsedArtifact(pId);
        switch (artifact)
        {
            case "AL": bonus += 0.1; break;
            case "GL": bonus += 0.2; break;
            case "AML": bonus += 0.3; break;
            case "BHS": bonus += 0.45; break;
            case "AF": bonus -= 0.45; break;
            case "MOT": bonus += 0.2; break;
        }
        if (PlayerHasSpecialBulding(pId, SpecialBuldings.ScienceNetwork) == true)
        {
            bonus += 0.15;
        }
        return bonus;
    }
    public static double GetBonusForGrowth(int pId)
    {
        double bonus = 0;
        //get bonus from race
        double prodBonus = double.Parse(StoredProcedures.GetConfigurationForOption("GrowthBonus"));
        bonus += prodBonus * GetPlayerInt(pId, "Growth").Value;
        string artifact = UsedArtifact(pId);
        switch (artifact)
        {
            case "E1": bonus += 0.1; break;
            case "E2": bonus += 0.2; break;
            case "E3": bonus += 0.3; break;
            case "STr": bonus += 0.2; break;
            case "BHS": bonus -= 0.1; break;
            case "AF": bonus -= 0.45; break;
            case "MOT": bonus += 0.2; break;
        }
        return bonus;
    }
    public static int SciencePercentage(int pId, string science)
    {
        return (int)(100 * (UserDefinedFunctions.GetPlayerFloat(pId, science).Value - (int)UserDefinedFunctions.GetPlayerFloat(pId, science).Value));
    }
    public static string UsedArtifact(int pId)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Select ArtifactId From Players Where PlayerId = @pId", conn))
            {

                cmd.Parameters.Add("@pId", SqlDbType.Int).Value = pId;
                return cmd.ExecuteScalar() as string;
            }
        }
    }
    public static List<string> PossibleArtifacts(int pId)
    {
        List<string> bought = PlayerArtifacts(pId);
        List<string> tmp = new List<string>();
        foreach (string t in ArtifactsFunctions.ArtifactsList)
        {
            if (ArtifactsFunctions.Prerequisites(pId, t) == true && bought.Contains(t) == false)
                tmp.Add(t);
        }
        return tmp;
    }
    public static List<string> PlayerArtifacts(int pId)
    {
        List<string> tmp = new List<string>();
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Select ArtifactId From Artifacts Where PlayerId = @pId", conn))
            {

                cmd.Parameters.Add("@pId", SqlDbType.Int).Value = pId;
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                    tmp.Add(rdr.GetString(0));
            }
        }
        return tmp;
    }
    public static bool PlayerHasSpecialBulding(int pId, SpecialBuldings type)
    {
        string command;
        switch (type)
        {
            case SpecialBuldings.SpyAcademy: command = "Select Academy From Players where PlayerId = @pId";
                break;
            case SpecialBuldings.OrbitalCC: command = "Select OrbitalCenter From Players where PlayerId = @pId";
                break;
            case SpecialBuldings.AllianceCC: command = "Select AllianceCenter From Players where PlayerId = @pId";
                break;
            case SpecialBuldings.Market: command = "Select Market From Players where PlayerId = @pId";
                break;
            case SpecialBuldings.ScienceNetwork: command = "Select ScienceNetwork From Players where PlayerId = @pId";
                break;
            default:
                return false;
        }
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(command, conn))
            {
                string t = "";
                try
                {
                    cmd.Parameters.Add("@pId", SqlDbType.Int).Value = pId;
                    t = cmd.ExecuteScalar().ToString();
                    return bool.Parse(t);
                    //if (i == 0) return false;
                    //else return true;
                }
                catch (Exception ex)
                {
                    throw new Exception(t, ex);
                }
                finally { conn.Close(); }
            }
        }
    }
    public static Dictionary<string, string> GetPlayerPlanets(int pId)
    {
        Dictionary<string, string> tmp = new Dictionary<string, string>();
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(@"SELECT Systems.SystemName, Planets.PlanetID, Planets.SystemID FROM Systems INNER JOIN Planets ON Systems.SystemID = Planets.SystemID WHERE (Planets.OwnerID = @oId)", conn))
            {
                cmd.Parameters.Add("@oId", SqlDbType.Int).Value = pId;
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    tmp.Add(rdr[2].ToString() + ";" + rdr[1].ToString(), rdr[0].ToString() + " " + rdr[1].ToString());
                }
            }
        }
        return tmp;
    }
    public static int EcoBonus(int pId)
    {
        int eco = (int)GetPlayerFloat(pId, "Economy").Value;
        return (int)Math.Floor(eco / 4.0);
    }
    public static int PlayerCurrentScience(int id)
    {
        return GetPlayerInt(id, "CurrentScience").Value;
    }
    public static string PlayerName(int id)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(@"SELECT     PlayerName
FROM         dbo.Players
WHERE     (PlayerId  = @pId )
", conn))
            {
                cmd.Parameters.Add("@pId", SqlDbType.Int).Value = id;
                object o = cmd.ExecuteScalar();
                if (o != null)
                    return o.ToString();
                else
                    return string.Empty;

            }
        }
    }
    public static bool UserExists(string name)
    {
        {
            using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(@"SELECT     Count(*)
FROM         dbo.Players
WHERE     (PlayerName  = @PlayerName )
", conn))
                {
                    cmd.Parameters.Add("@PlayerName", SqlDbType.NVarChar).Value = name;
                    object c = cmd.ExecuteScalar();
                    int count = int.Parse(c.ToString());
                    if (count > 0) return true;
                    else return false;
                }
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlInt32 PlayerPlanets(int id)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT Count( OwnerID) FROM dbo.Planets Where OwnerId = @id", conn))
            {
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
                return int.Parse(cmd.ExecuteScalar().ToString());
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlInt32 PlayerCulture(int id)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT CurrentCulture FROM dbo.Players Where PlayerId = @id", conn))
            {
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
                return (int)float.Parse(cmd.ExecuteScalar().ToString());
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlInt32 MaxPopulationForPlanet(int s)
    {
        if (s < 2) return 5;
        else if (s < 4) return 6;
        else if (s < 6) return 7;
        else if (s < 8) return 8;
        else if (s < 10) return 9;
        else return s;
    }
    public static void PlayerCapital(int pId, out int sId, out int plId)
    {
        sId = -1; plId = -1;
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT top(1) SystemId, PlanetId  FROM dbo.Planets Where OwnerId = @id Order by populationLevel desc, Planets.PopToNextLevel ASC", conn))
            {
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = pId;
                SqlDataReader rdr = cmd.ExecuteReader();
                rdr.Read();
                sId = rdr.GetInt32(0);
                plId = rdr.GetInt32(1);
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlDouble GetPlayerCash(int id)
    {
        return GetPlayerFloat(id, "Cash");
    }
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlInt32 GetPlayerInt(int id, string property)
    {
        try
        {
            using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("SELECT " + property + "  FROM dbo.Players Where PlayerId = @id", conn))
                {
                    cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;

                    int s = int.Parse(cmd.ExecuteScalar().ToString());
                    return s;
                }
            }
        }
        catch (Exception)
        {
            StoredProcedures.InsertNews(8, DateTime.Now, "Request: " + id.ToString() + " " + property + " failed");
            return 0;
        }
    }
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlDouble GetPlayerFloat(int id, string property)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT " + property + "  FROM dbo.Players Where PlayerId = @id", conn))
            {
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;

                double s = double.Parse(cmd.ExecuteScalar().ToString());
                return s;
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlInt32 GetPlayerID(string name)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT PlayerId FROM dbo.Players Where PlayerName = @id", conn))
            {
                try
                {
                    cmd.Parameters.Add("@id", SqlDbType.NVarChar).Value = name;

                    int s = int.Parse(cmd.ExecuteScalar().ToString());
                    return s;
                }
                catch (Exception ex)
                {
                    throw new Exception("GetPlayerID(" + name + ")", ex);
                }
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlInt32 GetPlayerSciencePerH(int pId)
    {
        try
        {
            using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("SELECT     CAST(SUM(Labs) + SUM(PopulationLevel) AS int) AS Expr2 FROM         dbo.Planets GROUP BY OwnerID HAVING      (OwnerID = @pId)", conn))
                {
                    cmd.Parameters.Add("@pId", SqlDbType.Int).Value = pId;
                    int t;
                    if (int.TryParse(cmd.ExecuteScalar().ToString(), out t) == true)
                        //TODO: Get bonus and modify the value by it
                        return t;
                    else
                        return 0;
                }
            }
        }
        catch (Exception)
        {
            return 0;
        }
    }
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlInt32 GetPlayerProductionPerH(int pId)
    {
        try
        {
            using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("SELECT     CAST(SUM(Factories) + SUM(PopulationLevel) AS int) AS Expr2 FROM         dbo.Planets GROUP BY OwnerID HAVING      (OwnerID = @pId)", conn))
                {
                    cmd.Parameters.Add("@pId", SqlDbType.Int).Value = pId;
                    int t;
                    if (int.TryParse(cmd.ExecuteScalar().ToString(), out t) == true)
                        //TODO: Get bonus and modify the value by it
                        return t;
                    else
                        return 0;
                }
            }
        }
        catch (Exception)
        {
            return 0;
        }
    }
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlInt32 GetPlayerCulturePerH(int pId)
    {
        try
        {
            using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("SELECT     CAST( SUM(Cybernets) AS int) AS Expr2 FROM         dbo.Planets GROUP BY OwnerID HAVING      (OwnerID = @pId)", conn))
                {
                    cmd.Parameters.Add("@pId", SqlDbType.Int).Value = pId;
                    int t;
                    if (int.TryParse(cmd.ExecuteScalar().ToString(), out t) == true)
                        //TODO: Get bonus and modify the value by it
                        return t;
                    else
                        return 0;
                }
            }
        }
        catch (Exception)
        {
            return 0;
        }

    }
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlDouble GetPlayerScience(int pId, int scienceID)
    {
        object t = null;
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("", conn))
            {
                cmd.Parameters.Add("@pId", SqlDbType.Int).Value = pId;
                switch (scienceID)
                {
                    case 1:
                        cmd.CommandText = "SELECT     Biology FROM         dbo.Players Where PlayerId = @pID"
                        ; break;
                    case 2:
                        cmd.CommandText = "SELECT     Economy FROM         dbo.Players Where PlayerId = @pID"
                        ; break;
                    case 3:
                        cmd.CommandText = "SELECT     Energy FROM         dbo.Players Where PlayerId = @pID"
                        ; break;
                    case 4:
                        cmd.CommandText = "SELECT     Math FROM         dbo.Players Where PlayerId = @pID"
                        ; break;
                    case 5:
                        cmd.CommandText = "SELECT     Physics FROM         dbo.Players Where PlayerId = @pID"
                        ; break;
                    case 6:
                        cmd.CommandText = "SELECT     Social FROM         dbo.Players Where PlayerId = @pID"
                        ; break;
                }
                try
                {
                    t = cmd.ExecuteScalar();
                    return double.Parse(t.ToString());
                }
                catch (Exception ex)
                {
                    throw new Exception(t.ToString(), ex);
                }
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlInt32 GetPlayerFleetsOnAir(int pId)
    {
        using (SqlConnection conn = new SqlConnection(StaticMethods.ConnectionString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("Select Count(*) from Fleets Where OwnerId = @pId", conn))
            {
                cmd.Parameters.Add("@pId", SqlDbType.Int).Value = pId;
                return int.Parse(cmd.ExecuteScalar().ToString());
            }
        }
    }
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlInt32 GetPlayerSciencePerHWithBonus(int pId)
    {
        int sci = GetPlayerSciencePerH(pId).Value;
        double bonus = GetBonusForScience(pId);
        return (int)(sci * (1 + bonus));
    }
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlInt32 GetPlayerCulturePerHWithBonus(int pId)
    {
        int cul = GetPlayerCulturePerH(pId).Value;
        double bonus = GetBonusForCulture(pId);
        return (int)(cul * (1 + bonus));
    }
};

